using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Data;

namespace SqlScriptCreator.Generators
{
    public class TableFunctionGenerator : IScriptGenerator  
    {
        private String _funtionName = "dbo.myFunction";

        [CategoryAttribute("Function settings")]
        public String FunctionName  
        {
            get { return _funtionName; }
            set { _funtionName = value; }
        }

        private String _arguments = "@someIdRef INT";

        [CategoryAttribute("Function settings")]
        public String Arguments
	    {
		    get { return _arguments;}
		    set { _arguments = value;}
	    }

        private String _tableName = "myTable";

        [CategoryAttribute("Table settings"),
         DescriptionAttribute("The name of the generated table variable (@TABLENAME).")]
        public string TableName
        {
            get
            {
                return _tableName;
            }
            set
            {
                _tableName = value;
            }
        }

        private String _columnPrefix;

        [CategoryAttribute("Table settings"),
         DescriptionAttribute("Characters that will be prepended to every generated table column.")]
        public string ColumnPrefix
        {
            get
            {
                return _columnPrefix;
            }
            set
            {
                _columnPrefix = value;
            }
        }

        private String _columnSuffix;

        [CategoryAttribute("Table settings"),
         DescriptionAttribute("Characters that will be appended to every generated table column.")]
        public string ColumnSuffix
        {
            get
            {
                return _columnSuffix;
            }
            set
            {
                _columnSuffix = value;
            }
        }

        #region IScriptGenerator Members

        override public string ToString()
        {
            return "Create table-function";
        }

        public string generateScript(String query, DataTable dtQueryResult, DataTable dtParameters)
        {
            StringBuilder sb = new StringBuilder();
            String[] fieldnames = new String[dtParameters.Rows.Count];
            String lineend;
            String lengthSpecification;

            sb.AppendLine("CREATE FUNCTION "+FunctionName+"("+Arguments+")");
            sb.AppendLine("RETURNS @" + TableName + " TABLE (");
            for (int i = 0; i < dtParameters.Rows.Count; i++)
            {
                DataRow row = dtParameters.Rows[i];
                int columnSize = Convert.ToInt32(row["ColumnSize"]);
                String dataType = row["DataTypeName"].ToString();
                String columnName = row["BaseColumnName"].ToString();

                if (i + 1 < dtParameters.Rows.Count)
                    lineend = ",";
                else
                    lineend = "";

                fieldnames[i] = ColumnPrefix + columnName + ColumnSuffix;
                sb.AppendLine("\t" + fieldnames[i] + " " + SqlTools.getDatatypeExpression(dataType, columnSize)
                    + lineend);
            }
            sb.AppendLine(")");
            sb.AppendLine("AS");
            sb.AppendLine("BEGIN");
            sb.AppendLine("");
            sb.Append("\tINSERT INTO @" + TableName + " (");
            sb.Append(String.Join(", ", fieldnames));
            sb.AppendLine(")");
            sb.AppendLine("\t"+query);
            sb.AppendLine("");
            sb.AppendLine("\tRETURN");
            sb.AppendLine("END");

            return sb.ToString();
        }

        #endregion
    }
}
